package io.renren.modules.sys.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import io.renren.common.utils.PageUtils;
import io.renren.common.utils.Query;
import io.renren.modules.sys.dao.ColumnsInfoDao;
import io.renren.modules.sys.dao.TableInfoDao;
import io.renren.modules.sys.entity.ColumnsInfoEntity;
import io.renren.modules.sys.entity.DatabaseinfoEntity;
import io.renren.modules.sys.entity.TableInfoEntity;
import io.renren.modules.sys.service.TableInfoService;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.*;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Service("tableInfoService")
public class TableInfoServiceImpl extends ServiceImpl<TableInfoDao, TableInfoEntity> implements TableInfoService {
    private final Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private TableInfoDao tableInfoDao;
    @Autowired
    private ColumnsInfoDao columnsInfoDao;


    @Override
    public PageUtils queryPage(Map<String, Object> params) {

        String connid = (String) params.get("connid");

        IPage<TableInfoEntity> page = this.page(
                new Query<TableInfoEntity>().getPage(params),
                new QueryWrapper<TableInfoEntity>()
                        .eq(StringUtils.isNotBlank(connid), "db_id", connid)


        );

        return new PageUtils(page);
    }

    @Override
    public List<TableInfoEntity> mysqlQueryTable(Connection conn, List<TableInfoEntity> tables, DatabaseinfoEntity databaseinfoEntity, String connid) {
        DatabaseMetaData dbMetaData = null;
        ResultSet rs = null;
        try {
            dbMetaData = conn.getMetaData();
            rs = dbMetaData.getTables(null, null, null, new String[]{"TABLE"});
            int i = 1;
            while (rs.next()) {
                if (rs.getString("TABLE_CAT").equals(databaseinfoEntity.getDbname()) || rs.getString("TABLE_CAT") == databaseinfoEntity.getDbname()) {
                    TableInfoEntity tablesInfoEntity = new TableInfoEntity();
                    tablesInfoEntity.setId(i);
                    tablesInfoEntity.setDbId(connid);
                    tablesInfoEntity.setTableName(rs.getString("TABLE_NAME"));
                    tablesInfoEntity.setRemarks(rs.getString("REMARKS"));
                    tables.add(tablesInfoEntity);
                    i++;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tables;
    }

    @Override
    public List<TableInfoEntity> pgsqlQueryTable(Connection conn, List<TableInfoEntity> tables, DatabaseinfoEntity databaseinfoEntity, String connid) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from pg_tables";

        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            int i = 0;
            while (rs.next()) {
                i++;

                String schemaname = rs.getString("schemaname");
                String tablename = rs.getString("tablename");
                if (schemaname.equals("public") || schemaname == "public") {
                    TableInfoEntity tablesInfoEntity = new TableInfoEntity();
                    tablesInfoEntity.setId(i);
                    tablesInfoEntity.setDbId(connid);
                    tablesInfoEntity.setTableName(tablename);
                    tablesInfoEntity.setRemarks(schemaname);
                    tables.add(tablesInfoEntity);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return tables;
    }

    @Override
    @Transactional
    public HashMap mysqlQueryColumns(Connection conn, String tableId, String tableName) {

        int num = columnsInfoDao.delColumns(tableId);
        HashMap map = new HashMap();
        List<ColumnsInfoEntity> columns = new ArrayList<>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT column_name," +
                "column_comment,IS_NULLABLE," +
                "data_type," +
                "CHARACTER_MAXIMUM_LENGTH " +
                "FROM information_schema.COLUMNS WHERE table_name = '" + tableName + "'";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            int i = 0;
            while (rs.next()) {
                i++;
                ColumnsInfoEntity columnsEntity = new ColumnsInfoEntity();
                columnsEntity.setId(i);
                columnsEntity.setTableId(tableId);
                columnsEntity.setColumnsName(rs.getString("column_name"));
                columnsEntity.setColumnsType(rs.getString("data_type"));
                columnsEntity.setColumnsLength(rs.getString("CHARACTER_MAXIMUM_LENGTH"));
                columnsEntity.setIsNullable(rs.getString("IS_NULLABLE"));
                columnsEntity.setColumnsZs(rs.getString("column_comment"));
                columnsEntity.setSort(i);
                columnsInfoDao.insert(columnsEntity);
                columns.add(columnsEntity);
            }
            map.put("0", tableName);
            map.put("1", columns);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return map;
    }

    @Override
    public HashMap pgsqlQueryColumns(Connection conn, String tableId, String tableName) {
        int num = columnsInfoDao.delColumns(tableId);
        HashMap map = new HashMap();
        List<ColumnsInfoEntity> columns = new ArrayList<>();
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "SELECT a.attname ,a.attnotnull," +
                "col_description(a.attrelid,a.attnum) ," +
                "t.typname ," +
                "SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\\(.*\\)') , " +
                "concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\\(.*\\)')) " +
                "FROM  pg_class as c,pg_attribute as a, pg_type as t " +
                "WHERE  c.relname = '" + tableName + "' and a.atttypid = t.oid and a.attrelid = c.oid and a.attnum>0";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            int i = 0;
            while (rs.next()) {
                i++;

                ColumnsInfoEntity columnsEntity = new ColumnsInfoEntity();
                columnsEntity.setId(i);
                columnsEntity.setTableId(tableId);
                columnsEntity.setColumnsName(rs.getString("attname"));
                columnsEntity.setColumnsType(rs.getString("typname"));
                columnsEntity.setColumnsLength(rs.getString("substring"));
                columnsEntity.setIsNullable(rs.getString("attnotnull"));
                columnsEntity.setColumnsZs(rs.getString("col_description"));
                columnsEntity.setSort(i);
                columnsInfoDao.insert(columnsEntity);
                columns.add(columnsEntity);

            }
            map.put("0", tableName);
            map.put("1", columns);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return map;
    }

    @Override
    public List<TableInfoEntity> oracleQueryTable(Connection conn, List<TableInfoEntity> tables, DatabaseinfoEntity databaseinfoEntity, String connid) {
        String sql = "select TABLE_NAME from all_tables WHERE owner='" + databaseinfoEntity.getConnname() + "'";
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            int count = 0;
            while (rs.next()) {
                count++;
                String tablename = rs.getString("TABLE_NAME");
                TableInfoEntity tablesInfoEntity = new TableInfoEntity();
                tablesInfoEntity.setId(count);
                tablesInfoEntity.setDbId(connid);
                tablesInfoEntity.setTableName(tablename);
                tablesInfoEntity.setRemarks("");
                tables.add(tablesInfoEntity);
            }
            rs.close();
            stmt.close();
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tables;
    }


    @Override
    public HashMap oracleQueryColumns(Connection conn, String tableId, String tableName) {
        int num = columnsInfoDao.delColumns(tableId);
        HashMap map = new HashMap();
        List<ColumnsInfoEntity> columns = new ArrayList<>();
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "select column_name from user_col_comments  where table_name = '" + tableName + "'";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            int i = 0;
            while (rs.next()) {
                i++;
                ColumnsInfoEntity columnsEntity = new ColumnsInfoEntity();
                columnsEntity.setId(i);
                columnsEntity.setTableId(tableId);
                columnsEntity.setColumnsName(rs.getString("column_name"));
                columnsEntity.setColumnsType("");
                columnsEntity.setColumnsLength("");
                columnsEntity.setIsNullable("");
                columnsEntity.setColumnsZs("");
                columnsInfoDao.insert(columnsEntity);
                columns.add(columnsEntity);
            }

            map.put("0", tableName);
            map.put("1", columns);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return map;
    }

    @Override
    @Async
    public void upCount(Connection conn, String tableName, String tableid) {

        PreparedStatement psByCount = null;
        ResultSet rsByCount = null;
        int count = 0;
        String sqlCount = "select count(*) from " + tableName;
        try {
            psByCount = conn.prepareStatement(sqlCount);
            rsByCount = psByCount.executeQuery();
            if (rsByCount.next()) {
                count = rsByCount.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rsByCount.close();
                psByCount.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        int num = tableInfoDao.upCount(tableid, count);

        if (num > 0) {
            logger.info("表：" + tableName + "生成数据量完成！");
        }

    }

    @Override
    public List<TableInfoEntity> getTableByDbid(String dbid) {

        List<TableInfoEntity> list = new ArrayList<>();
        list = tableInfoDao.getTableByDbid(dbid);
        return list;
    }

    @Override
    public int delTables(String dbid) {
        int num = tableInfoDao.delTables(dbid);
        return num;
    }

    @Override
    @Async
    public void upTcl(String dbType, Connection conn, String tableId, String tableName, HashMap map1) {

        PreparedStatement psByCount = null;
        ResultSet rsByCount = null;
        String sqlCount = "select count(*) from " + tableName;
        int count = 0;
        try {
            psByCount = conn.prepareStatement(sqlCount);
            rsByCount = psByCount.executeQuery();

            if (rsByCount.next()) {
                count = rsByCount.getInt(1);//ResultSet结果集中的第一个参数就是记录数，其实结果集中只有一个参数
            }

            List<ColumnsInfoEntity> lists = (List<ColumnsInfoEntity>) map1.get("1");

            for (ColumnsInfoEntity ce : lists) {

                PreparedStatement psByTcl = null;
                ResultSet rsByTcl = null;
                String sqlTcl = "";
                if (dbType.equals("mysql") || dbType == "dbType") {
                    sqlTcl = "SELECT COUNT(*) from " + tableName + "  where `" + ce.getColumnsName() + "` is not  NULL ";
                } else {
                    sqlTcl = "SELECT COUNT(*) from " + tableName + "  where " + ce.getColumnsName() + " is not  NULL ";
                }

                psByTcl = conn.prepareStatement(sqlTcl);
                rsByTcl = psByTcl.executeQuery();

                int countByTcl = 0;
                if (rsByTcl.next()) {
                    countByTcl = rsByTcl.getInt(1);//ResultSet结果集中的第一个参数就是记录数，其实结果集中只有一个参数
                }
                rsByTcl.close();
                psByTcl.close();
                NumberFormat numberFormat = NumberFormat.getInstance();
                String result = numberFormat.format((float) countByTcl / (float) count * 100);
//                columnsEntity.setTcl(result + "%");
                int num = 0;
                if (count > 0) {
                    num = columnsInfoDao.upTcl(String.valueOf(ce.getId()), result + "%");
                } else {
                    num = columnsInfoDao.upTcl(String.valueOf(ce.getId()), "0%");
                }

                if (num > 0) {
                    logger.info("表：" + tableName + ",列：" + ce.getColumnsName() + "生成填充率完成！");
                }
                rsByTcl.close();
                psByTcl.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rsByCount.close();
                psByCount.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
